<?php       
       $begin=$_GET['begin'];
       $end=$_GET['end'];
       $div=$_GET['disv'];
       /*
       $begin=1964;
       $end=2014;
       $div="divan";
        */
       
        
       $fd=  fopen("archivos/datos.txt", "w");
        //Se almacena la conexion en la variable $con
        
        $con=mysqli_connect("192.168.1.10","srojas","anemona") or die("Failed to connect with database!!!!");
        mysqli_select_db( $con,"hermes2"); 
        
      
        
        // The Chart table contains two fields: publicacion_year and publicaciones
        //$sth = mysql_query("SELECT publicacion_year, count(*) as publicaciones  FROM papers_vs_citas GROUP BY publicacion_year");
        //}else {
        if($div==="total"){
            //$sth = mysqli_query($con,"SELECT publicacion_year, count(*) as publicaciones  FROM papers_vs_citas GROUP BY publicacion_year HAVING publicacion_year>=$begin and publicacion_year<=$end");
            $sth = mysqli_query($con,"select res.publicacion_year as publicacion_year, publicaciones, (res.publicaciones/res.total) as ten from
                    (SELECT publicacion_year, count(*) as publicaciones,total FROM (select distinct titulo,publicacion_year from papers_vs_citas) as pc left outer join
                    (select anio,total from titular_assoc) as ta on pc.publicacion_year=ta.anio  
                    GROUP BY publicacion_year HAVING publicacion_year>=$begin and publicacion_year<=$end) as res;");

        
        mysqli_close($con);

        
       
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'publicacion_year', 'type' => 'string'),
        array('label' => 'Publicaciones', 'type' => 'number'),
        array('label' => 'Pub/Inv', 'type' => 'number')

        );
        
        fwrite($fd, "Anio_publicacion   num_publicaciones   promedio pub/inv"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,"\t".$r['publicacion_year']."\t");
            fwrite($fd,"\t".$r['publicaciones']."\t");
            fwrite($fd, $r['ten']."\r\n");
            
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string) utf8_encode($r['publicacion_year'])); 

        // Values of each slice
        $temp[] = array('v' => (int) $r['publicaciones']); 
        $temp[] = array('v' => (float) $r['ten']); 
        $rows[] = array('c' => $temp);//pc.doi is not null
        }

         }elseif($div==="divann"){
            $sth = mysqli_query($con,"select ib.publicacion_year as pubano, ib.publicaciones as ibp, nc.publicaciones as ncp from
                                (SELECT publicacion_year, count(*) as publicaciones  FROM (select * from hermes2.papers_vs_citas group by titulo) as pc join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id
                                join ifcweb_investigadores.departamentos as dp on p.id_depto=dp.id join ifcweb_investigadores.divisiones as dv on dp.id_division=dv.id  
                                where dv.id=1  GROUP BY publicacion_year HAVING publicacion_year>=$begin and publicacion_year<=$end) as ib
                                left outer join
                                (SELECT publicacion_year, count(*) as publicaciones FROM (select * from hermes2.papers_vs_citas group by titulo) as pc join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id
                                join ifcweb_investigadores.departamentos as dp on p.id_depto=dp.id join ifcweb_investigadores.divisiones as dv on dp.id_division=dv.id  
                                where dv.id=2  GROUP BY publicacion_year HAVING publicacion_year>=$begin and publicacion_year<=$end) as nc
                                on ib.publicacion_year=nc.publicacion_year");
            
            
            mysqli_close($con);

        
       
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'publicacion_year', 'type' => 'string'),
        array('label' => 'Investigacion Basica', 'type' => 'number'),
        array('label' => 'Neurociencias','type' => 'number')

        );
        
        fwrite($fd, "Anio_publicacion   num_publicaciones"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,"\t".$r['pubano']."\t");
            fwrite($fd,"\t".$r['ibp']."\t");
            fwrite($fd, $r['ncp']."\r\n");
            
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string) utf8_encode($r['pubano'])); 

        // Values of each slice
        $temp[] = array('v' => (int) $r['ibp']);
        $temp[] = array('v' => (int) $r['ncp']);
        
        $rows[] = array('c' => $temp);
        }
        
         }
        $table['rows'] = $rows;
        

        fclose($fd);
   
        echo json_encode($table);
       
        
        
        ?>


